package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.Teacher;
import cn.ztydata.teachingsystem.heaven.entity.TeacherTeam;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 教师团队Dao类
 * Created by chenxin on 2014/12/3.
 */
@Repository
public class TeacherTeamDao extends BaseDao {
    /**
     * 获取指定教师的任课数量
     * @param teacherIdList 教师ID集合
     * @return List
     *
     * @author cx
     * @since 2014-12-03
     */
    public List<Map<String, Object>> getCourseTotal(final List<Long> teacherIdList) {
        if (teacherIdList == null || teacherIdList.isEmpty()) {
            log.debug("教师ID集合为空:teacherIdList == null || teacherIdList.isEmpty()");
            return new ArrayList<>();
        }

        String sql = "SELECT iTeacherId AS teacherId,COUNT(iCourseId) AS total FROM t_teacher_team WHERE iTeacherId IN (:idList) GROUP BY iTeacherId";

        log.debug("获取指定教师的任课数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("idList", teacherIdList));
    }

    /**
     * 查询课程教师团队
     * @param courseIdList 课程ID集合
     * @return List
     *
     * @author cx
     * @since 2014-12-11
     */
    public List<TeacherTeam> getAllForTeam(final List<Long> courseIdList) {
        String sql = "SELECT tt.iCourseId,tt.tiRole,t.sName,t.sPicture FROM t_teacher_team tt LEFT JOIN t_teacher t ON tt.iTeacherId=t.id " +
                "WHERE tt.iCourseId IN (:courseIdList) ORDER BY tiRole";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseIdList", courseIdList);

        log.debug("查询课程教师团队");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<TeacherTeam>() {
            @Override
            public TeacherTeam mapRow(ResultSet rs, int rowNum) throws SQLException {
                TeacherTeam entity = new TeacherTeam();

                Teacher teacher = new Teacher();
                teacher.setName(rs.getString("sName"));
                teacher.setPicture(rs.getString("sPicture"));

                entity.setCourseId(rs.getLong("iCourseId"));
                entity.setRole(rs.getInt("tiRole"));
                entity.setTeacherInfo(teacher);

                return entity;
            }
        });
    }

    /**
     * 新增教师团队成员
     * @param teacherTeam 教师团队实体类
     * @return boolean
     *
     * @author cx
     * @since 2014-12-13
     */
    public boolean save(TeacherTeam teacherTeam) {
        String sql = "INSERT INTO t_teacher_team VALUES(:courseId,:teacherId,:role)";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", teacherTeam.getCourseId())
                .addValue("teacherId", teacherTeam.getTeacherId())
                .addValue("role", teacherTeam.getRole());

        log.debug("save new memeber");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 保存教师团队成员
     * @param courseId 课程ID
     * @param teacherId 教师ID
     * @param role 团队角色
     * @return boolean
     *
     * @author cx
     * @since 2014-12-11
     */
    public boolean save(final long courseId, final long teacherId, final int role) {
        String sql = "INSERT INTO t_teacher_team VALUES(:courseId,:teacherId,:role)";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId)
                .addValue("teacherId", teacherId)
                .addValue("role", role);

        log.debug("保存教师团队成员");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 移除教师团队成员
     * @param courseId 课程ID
     * @param teacherId 教师ID
     * @return boolean
     *
     * @author cx
     * @since 2014-12-13
     */
    public boolean delete(final long courseId, final long teacherId) {
        String sql = "DELETE FROM t_teacher_team WHERE iCourseId=:courseId AND iTeacherId=:teacherId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId)
                .addValue("teacherId", teacherId);

        log.debug("remove member");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 删除团队成员（除了课程负责人）
     *
     * @author chenxin
     * @since 2014/12/30
     */
    public void deleteExcludeLeader(final long courseId, final long teacherId) {
        String sql = "DELETE FROM t_teacher_team WHERE iCourseId=:courseId AND iTeacherId=:teacherId AND tiRole<>0";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId)
                .addValue("teacherId", teacherId);

        log.debug("delete course teacher team");

        namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 查询团队教师
     * @param courseId 课程ID
     * @param pageNum 页码
     * @param pageSize 每页条数
     * @return List
     *
     * @author cx
     * @since 2014-12-13
     */
    public Page<TeacherTeam> getTeachers(final long courseId, final int pageNum, final int pageSize) {
        String sql = "SELECT iTeacherId,tiRole FROM t_teacher_team WHERE iCourseId=:courseId ORDER BY tiRole";
        String countSql = "SELECT COUNT(*) FROM t_teacher_team WHERE iCourseId=:courseId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId);
        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql, sps, Integer.class);
        if (total == 0) {
            log.debug("团队教师无成员:total == 0");
            return new Page<>();
        }

        //追加limit限制
        sql += getLimitStr(pageNum, pageSize);

        List<TeacherTeam> teamList = namedParameterJdbcTemplate.query(sql, sps, new RowMapper<TeacherTeam>() {
            @Override
            public TeacherTeam mapRow(ResultSet rs, int rowNum) throws SQLException {
                TeacherTeam entity = new TeacherTeam();

                entity.setTeacherId(rs.getLong("iTeacherId"));
                entity.setRole(rs.getInt("tiRole"));

                return entity;
            }
        });

        log.debug("获取团队教师分页列表");
        return new Page<>(total, pageSize, pageNum, teamList);
    }

    /**
     * 查询团队教师
     * @param courseId 课程ID
     * @return List
     *
     * @author cx
     * @since 2014-12-15
     */
    public List<TeacherTeam> getTeachersForRole(final long courseId) {
        String sql = "SELECT iTeacherId,tiRole FROM t_teacher_team WHERE iCourseId=:courseId ORDER BY tiRole";

        log.debug("获取课程教师团队角色");
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("courseId", courseId), new RowMapper<TeacherTeam>() {
            @Override
            public TeacherTeam mapRow(ResultSet rs, int rowNum) throws SQLException {
                TeacherTeam entity = new TeacherTeam();

                entity.setTeacherId(rs.getLong("iTeacherId"));
                entity.setRole(rs.getInt("tiRole"));

                return entity;
            }
        });
    }

    /**
     * 查询教师所教课程ID
     * @param teacherId 教师ID
     * @return List
     *
     * @author chenxin
     * @since 2014/12/11
     */
    public List<Long> getCourseIds(final long teacherId) {
        String sql = "SELECT iCourseId FROM t_teacher_team WHERE iTeacherId=:teacherId";

        log.debug("查询教师所教课程ID");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("teacherId", teacherId), Long.class);
    }

    /**
     * 更新课程负责人
     *
     * @author chenxin
     * @since 2014/12/24
     */
    public boolean updateLeader(final long courseId, final long leaderId) {
        String sql = "UPDATE t_teacher_team SET iTeacherId=:leaderId WHERE iCourseId=:courseId AND tiRole=0";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("leaderId", leaderId)
                .addValue("courseId", courseId);

        log.debug("update course leader");

        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 获取教师团队成员
     * @param courseId 课程ID
     * @param teacherId 教师ID
     * @return TeacherTeam
     *
     * @author cx
     * @since 2015/03/11
     */
    public TeacherTeam getOne(final long courseId, final long teacherId) {
        String sql = "SELECT iCourseId,iTeacherId,tiRole FROM t_teacher_team WHERE iCourseId=:courseId AND iTeacherId=:teacherId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId).addValue("teacherId", teacherId);

        try {
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<TeacherTeam>() {
                @Override
                public TeacherTeam mapRow(ResultSet rs, int rowNum) throws SQLException {
                    TeacherTeam teacherTeam = new TeacherTeam();

                    teacherTeam.setCourseId(rs.getLong("iCourseId"));
                    teacherTeam.setTeacherId(rs.getLong("iTeacherId"));
                    teacherTeam.setRole(rs.getInt("tiRole"));

                    return teacherTeam;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            return null;
        }
    }

    /**
     * 查询课程教师团队
     * @param courseIdList 课程ID集合
     * @return List
     *
     * @author wyh
     * @since 2015-04-02
     */
    public List<TeacherTeam> getTeamByIds(final List<Long> courseIdList) {
        String sql = "SELECT tt.tiRole,tt.iCourseId,t.id,t.sName,t.sPicture FROM t_teacher_team tt JOIN t_teacher t ON tt.iTeacherId=t.id " +
                "WHERE tt.iCourseId IN (:courseIdList) ORDER BY tiRole";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseIdList", courseIdList);

        log.debug("查询课程教师团队");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<TeacherTeam>() {
            @Override
            public TeacherTeam mapRow(ResultSet rs, int rowNum) throws SQLException {
                TeacherTeam entity = new TeacherTeam();
                entity.setCourseId(rs.getLong("iCourseId"));
                entity.setTeacherId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setPhoto(rs.getString("sPicture"));
                return entity;
            }
        });
    }


    /**
     * 根据教师ID获取其维护的课程所在的专业ＩＤ集合
     *
     * @param teacherId 教师ID
     * @return list
     * @author lyj
     * @since 2015-5-6
     */
    public List<Long> getSpecialtiesByTeacherId(final long teacherId) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT c.iSpecialtyId  FROM t_teacher_team tt JOIN t_course c ON c.id = tt.iCourseId WHERE tt.iTeacherId = :teacherId");

        log.debug("根据教师ID获取其维护的课程所在的专业ＩＤ集合");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("teacherId", teacherId), Long.class);
    }


    /**
     * 根据课程ID集合获取每个课程教师团队成员的ID
     * @param courseIds 课程Id集合
     * @return List<Long>
     * @author lyj
     * @since 2015-5-6
     */
    public List<Long> getTeacherIdsByCourseId(final Set<Long> courseIds) {
        StringBuilder sql = new StringBuilder("SELECT iTeacherId FROM t_teacher_team WHERE iCourseId IN (:courseIds)");

        log.debug("根据课程ID集合获取每个课程教师团队成员的ID");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("courseIds", courseIds), Long.class);
    }
}
